package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.AdKanbanDto;
import com.dy.yunying.api.datacenter.vo.AdKanbanOverviewVo;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @author ：lile
 * @date ：2021/12/1 10:44
 * @description：
 * @modified By：
 */
@Slf4j
@Component(value = "dcAdKanbanDao")
public class AdKanbanDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 求条数
	 *
	 * @param req
	 * @return
	 */
	public Long countDataTotal(AdKanbanDto req) {
		StringBuilder sql = getSql(req);
		StringBuilder countSql = new StringBuilder();
		countSql.append(" select count(*) from ( ").append(sql).append(")");
		Long count = clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
		return count;
	}

	/**
	 * 看板查询list
	 *
	 * @param req
	 * @return
	 */
	public List<AdKanbanOverviewVo> selectAdKanbanSourceTable(AdKanbanDto req) {
		StringBuilder sql = getSql(req);

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append(" limit ").append(offset).append(",").append(size);
		}

		log.info("sql : [{}]", sql.toString());
		long start = System.currentTimeMillis();

		List<AdKanbanOverviewVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(AdKanbanOverviewVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "看板数据报表，耗时：" + (end - start) + "毫秒");
		return list;
	}


	private StringBuilder getSql(AdKanbanDto req) {

		StringBuilder queryColumnSql = this.getQueryColumnSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(" select \n");
		sql.append(this.getPeriodSql(req)).append(",");
		sql.append(this.getQueryColumnSql(req));
		sql.append("rudeCosts cost, --原始消耗 \n");
		sql.append("costs rudeCost, --返点后消耗 \n");
		sql.append("totalPayfee uuidsumfees, --累计充值金额 \n");
		sql.append("totalPaysharfee dividefees --累计分成后充值金额 \n");
		if (StringUtils.isNotBlank(queryColumnSql.toString()) && queryColumnSql.toString().contains("pgid")) {
			sql.append(",sharing \n");
		}
		sql.append(" from( \n");
		sql.append(this.getDevicePaySql(req));
		sql.append(" )a \n");
		sql.append(" FULL JOIN ( \n");
		sql.append(this.getCostSql(req));
		sql.append(" )b  \n");
		sql.append(" USING (  \n");
		// USING 条件
		sql.append(this.getPeriodSql(req));
		sql.append(this.getGroupColumnSql(req));
		sql.append(" ) \n");
		return sql;
	}


	private StringBuilder getCostSql(AdKanbanDto req) {

		StringBuilder costSql = new StringBuilder();

		costSql.append(" select \n ");
		costSql.append(this.getPeriodSql(req)).append(",");
		costSql.append(this.getQueryColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			costSql.append("     ").append(req.getQueryColumn()).append(",");
		}
		costSql.append(" coalesce(sum(rudeCost),0) rudeCosts, --原始消耗\n" +
				" coalesce(sum(cost),0) costs --返点后消耗\n");
		costSql.append(" from( ");

		costSql.append(" select day,week,month,year,collect,ad_show shownums,click clicknums,ad_id adid,ad_account advertiserid,coalesce(rude_cost,0) rudeCost,  coalesce(cost,0) cost from  ");
		costSql.append(yunYingProperties.getAdidrebatetable()).append(" ard \n ");
		// 消耗的 广告账号 日期过滤
		costSql.append(" where  \n ");
		costSql.append(" day >=").append(req.getRsTime());
		costSql.append(" and day <=").append(req.getReTime());
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 广告权限
			costSql.append(" and ad_account  IN (  \n");
			costSql.append(req.getAdAccounts());
			costSql.append("  ) \n");
		}
		costSql.append("  ) ard\n");
		costSql.append(" left join (select os,game_main pgid,game_sub gid,chl_main parentchl,chl_sub chl,chl_base appchl,ad_id from ");
		costSql.append(yunYingProperties.getAdptypetable());
		costSql.append(")  ap \n");
		costSql.append("  on ard.adid = ap.ad_id ");
		costSql.append(" left join (select manage investor,parent_code,chncode,dept_id deptId,dept_group_id userGroupId,real_name investorName,dept_name deptName,coalesce(name,'-') userGroupName from  v_dim_200_pangu_channel_user_dept_group");
		costSql.append(") wpc \n");
		costSql.append("   on wpc.parent_code = ap.parentchl \n ");
		costSql.append("    AND wpc.chncode = ap.chl \n ");

		costSql.append(" where 1=1 ");
		costSql.append(this.selectCommonCondition(req, ""));

		costSql.append(this.selectComConditionWd(req, ""));
		costSql.append(" group by  \n");
		// group by 条件
		costSql.append(this.getPeriodSql(req));
		costSql.append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			costSql.append(", ").append(req.getQueryColumn());
		}
		costSql.append(" HAVING rudeCost >0  \n");
		return costSql;
	}

	private StringBuilder getDevicePaySql(AdKanbanDto req) {
		StringBuilder UuidPaySql = new StringBuilder();

		UuidPaySql.append(" select \n ");

		UuidPaySql.append(this.getPeriodSql(req)).append(",");
		UuidPaySql.append(this.getQueryColumnSql(req));
		UuidPaySql.append(
				" coalesce(sum(reg.fee_total),0) totalPayfee, --累计充值金额\n" +
						" coalesce(sum(reg.fee_total *(1 - pg.sharing)),0) totalPaysharfee --累计分成后充值金额 \n");

		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains("pgid")) {
			UuidPaySql.append(",sharing  sharing\n");
		}
		UuidPaySql.append(" from( ");

		UuidPaySql.append(" select reg_day day,week,month,year,kid,collect,game_main pgid,os,game_sub gid,chl_main parentchl,chl_sub chl,chl_base appchl,ad_id adid,ad_account advertiserid,latest_username, is_2_retention, fee_1, givemoney_1, fee_week, givemoney_week, fee_month, givemoney_month, fee_total, givemoney_total from  ");
		UuidPaySql.append(yunYingProperties.getNinetydeviceregtable()).append(" \n");
		UuidPaySql.append(" where   \n ");
		UuidPaySql.append(" spread_type = 1 AND reg_day >=").append(req.getRsTime());
		UuidPaySql.append(" and  reg_day <=").append(req.getReTime());
		UuidPaySql.append(this.selectCommonCondition(req, " "));
		UuidPaySql.append(" ) reg \n ");
		UuidPaySql.append("left join (select CAST (id as Int16) as id,sharing from dim_200_pangu_mysql_parent_game pg_tmp) pg\n" +
				" on reg.pgid = pg.id \n");
		UuidPaySql.append(" left join (select manage investor,parent_code,chncode,dept_id deptId,dept_group_id userGroupId,real_name investorName,dept_name deptName,coalesce(name,'-') userGroupName from  v_dim_200_pangu_channel_user_dept_group ");
		UuidPaySql.append(" ) wpc \n" +
				" on wpc.parent_code = reg.parentchl \n" +
				" AND wpc.chncode = reg.chl \n");
		UuidPaySql.append(" where 1=1 ");
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			UuidPaySql.append(" and investor IN ( -- 管理的账号 \n");
			UuidPaySql.append(req.getUserIds());
			UuidPaySql.append(" ) \n");

		}
		UuidPaySql.append(this.selectComConditionWd(req, ""));
		UuidPaySql.append(" group by  \n");
		// group by 条件
		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains("pgid")) {
			UuidPaySql.append("sharing,\n");
		}
		UuidPaySql.append(this.getPeriodSql(req));
		UuidPaySql.append(this.getGroupColumnSql(req));
		if (org.apache.commons.lang.StringUtils.isNotBlank(req.getQueryColumn())) {
			UuidPaySql.append(" , ").append(req.getQueryColumn());
		}
		UuidPaySql.append(" HAVING totalPayfee >0  \n");
		return UuidPaySql;
	}

	//  分组条件
	private StringBuilder getGroupColumnSql(AdKanbanDto req) {
		String queryColumn = req.getQueryColumn();

		StringBuilder sql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();

		if (org.apache.commons.lang.StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("deptName");

			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(",");
				groupColumnSql.append("investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("userGroupName");

			}
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
		}
		sql.append(groupColumnSql);
		return sql;
	}


	//类别参数
	private StringBuilder getQueryColumnSql(AdKanbanDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		if (org.apache.commons.lang.StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName");
				queryColumnSql.append(",");
			}
			queryColumnSql.append(queryColumn);
			queryColumnSql.append(",");
		}
		sql.append(queryColumnSql);
		return sql;
	}

	// 查询条件
	public String selectCommonCondition(AdKanbanDto req, String bieming) {

		final String os = req.getOs();
		final String pgidArr = req.getPgIdArr();
		final String gameidArr = req.getGameIdArr();


		// 通用筛选条件
		StringBuffer commCondSB = new StringBuffer("");

		if (StringUtils.isNotBlank(os)) {
			commCondSB.append("                     AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append("                     AND pgid in (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append("                     AND gid in (" + gameidArr + ")");
		}

		String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);
		return commCondSB.toString();
	}


	public String selectComConditionWd(AdKanbanDto req, String bieming) {
		final String deptIdArr = req.getDeptIdArr();
		// 通用筛选条件
		StringBuffer commCondSB = new StringBuffer("");
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append("                     AND deptId IN (").append(deptIdArr).append(")");
		}
		String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);
		return commCondSB.toString();
	}

	//周期 周月
	private StringBuilder getPeriodSql(AdKanbanDto req) {

		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}


}
